const { getDbPool } = require('../infra/dbClient')
const db = getDbPool()
const { getLogger } = require('../logger')
const logger = getLogger('models.auditLogModel')

class AuditLogModel {
    static async create(data) {
        const sql = `
      INSERT INTO audit_logs (
        user_id, username, dept_id, dept_name,
        action_type, module_code, business_type, operation_desc,
        url, method,
        data_type, target_dept_id, target_dept_name, target_entity_type, target_entity_id, target_entity_name,
        client_ip, user_agent, location, browser, operating_system, referer, device_type,
        before_data, after_data, change_summary, operation_time, created_at
      ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())
    `
        const params = [
            data.user_id || null,
            data.username || '',
            data.dept_id || null,
            data.dept_name || null,
            data.action_type || '',
            data.module_code || '',
            data.business_type || null,
            data.operation_desc || null,
            data.url || '',
            data.method || '',
            data.data_type || 1,
            data.target_dept_id || null,
            data.target_dept_name || null,
            data.target_entity_type || null,
            data.target_entity_id || null,
            data.target_entity_name || null,
            data.client_ip || null,
            data.user_agent || null,
            data.location || null,
            data.browser || null,
            data.operating_system || null,
            data.referer || null,
            data.device_type || null,
            data.before_data ? JSON.stringify(data.before_data) : null,
            data.after_data ? JSON.stringify(data.after_data) : null,
            data.change_summary || null
        ]
        try {
            const [result] = await db.execute(sql, params)
            return result.insertId
        } catch (error) {
            logger.error('create audit log failed', { error })
            throw error
        }
    }


    // 获取全部审计日志列表
    static async getAllLogs(page = 1, limit = 20, filters = {}) {
        const offset = (page - 1) * limit
        let where = 'WHERE 1=1'
        let params = []
        if (filters.business_type) { where += ' AND business_type = ?'; params.push(filters.business_type) }
        if (filters.action_type) { where += ' AND action_type = ?'; params.push(filters.action_type) }
        if (filters.module_code) { where += ' AND module_code = ?'; params.push(filters.module_code) }
        if (filters.username) { where += ' AND username LIKE ?'; params.push(`%${filters.username}%`) }
        if (filters.dept_id) { where += ' AND dept_id = ?'; params.push(filters.dept_id) }
        if (filters.start_time) { where += ' AND operation_time >= ?'; params.push(filters.start_time) }
        if (filters.end_time) { where += ' AND operation_time <= ?'; params.push(filters.end_time) }

        const sql = `
            SELECT 
                a.id, 
                a.user_id, 
                a.username,
                (SELECT name FROM user WHERE id = a.user_id COLLATE utf8mb4_unicode_ci AND is_delete = 0) as opername,
                a.dept_id,
                a.dept_name,
                a.action_type,
                a.module_code,
                (SELECT m.name FROM menu m WHERE m.module_code COLLATE utf8mb4_unicode_ci = a.module_code COLLATE utf8mb4_unicode_ci AND m.is_delete = 0 LIMIT 1) as module_name,
                a.business_type,
                a.operation_desc,
                a.url,
                a.method,            
                a.client_ip,
                a.location,
                a.operating_system,
                a.operation_time,
                a.created_at
            FROM audit_logs a
            ${where}
            ORDER BY a.operation_time DESC
            LIMIT ${db.escape(limit)} OFFSET ${db.escape(offset)}
        `;

        try {
            const [rows] = await db.execute(sql, params)

            return rows
        } catch (error) {
            logger.error('获取审计日志列表失败', { error })
            throw error
        }
    }

    // 获取审计日志数量
    static async getLogCount(filters = {}) {
        let where = 'WHERE 1=1'
        let params = []
        if (filters.business_type) { where += ' AND business_type = ?'; params.push(filters.business_type) }
        if (filters.action_type) { where += ' AND action_type = ?'; params.push(filters.action_type) }
        if (filters.module_code) { where += ' AND module_code = ?'; params.push(filters.module_code) }
        if (filters.username) { where += ' AND username LIKE ?'; params.push(`%${filters.username}%`) }
        if (filters.dept_id) { where += ' AND dept_id = ?'; params.push(filters.dept_id) }
        if (filters.start_time) { where += ' AND operation_time >= ?'; params.push(filters.start_time) }
        if (filters.end_time) { where += ' AND operation_time <= ?'; params.push(filters.end_time) }
        const countSql = `SELECT COUNT(*) AS total FROM audit_logs ${where}`
        try {
            const [rows] = await db.execute(countSql, params)
            return rows[0].total
        } catch (error) {
            logger.error('get audit log count failed', { error })
            throw error
        }
    }

    // 根据ID获取审计日志详情
    static async getById(id) {

        const sql = `
            SELECT 
                a.id, 
                a.user_id, 
                a.username,
                (SELECT name FROM user WHERE id = a.user_id COLLATE utf8mb4_unicode_ci AND is_delete = 0) as opername,
                a.dept_id,
                a.dept_name,
                a.action_type,
                a.module_code,
                (SELECT m.name FROM menu m WHERE m.module_code COLLATE utf8mb4_unicode_ci = a.module_code COLLATE utf8mb4_unicode_ci AND m.is_delete = 0 LIMIT 1) as module_name,
                a.business_type,
                a.operation_desc,
                a.url,
                a.method,            
                a.client_ip,
                a.location,
                a.operating_system,
                a.operation_time,
                a.created_at,
                a.user_agent,
                a.browser,
                a.referer,
                a.device_type,
                a.before_data,
                a.after_data,
                a.change_summary

            FROM audit_logs a
            WHERE a.id = ?
        `;

        try {
            const [rows] = await db.execute(sql, [id])
            return rows[0] || null
        } catch (error) {
            logger.error('get audit log by id failed', { error })
            throw error
        }
    }

    static async getOnlineUsers(windowMinutes = 5, filters = {}) {
        const minutes = Number(windowMinutes) || 5
        const sql = `
            WITH latest_audit AS (
                SELECT 
                    a.*, 
                    ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY a.operation_time DESC, a.id DESC) AS rn
                FROM audit_logs a
                WHERE a.operation_time >= DATE_SUB(NOW(), INTERVAL ? MINUTE) AND a.user_id IS NOT NULL
            ),
            latest_token_valid AS (
                SELECT 
                    t.*, 
                    ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.last_active_time DESC, t.created_at DESC) AS rn
                FROM tokens t
                WHERE t.is_revoked = 0 AND t.expires_at > NOW()
            ),
            latest_token_any AS (
                SELECT 
                    t.*, 
                    ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.created_at DESC) AS rn
                FROM tokens t
                WHERE t.is_revoked = 0
            )
            SELECT 
                la.user_id,
                la.username,
                la.dept_id,
                la.dept_name,
                la.operation_time AS last_active_time,
                (
                    SELECT COUNT(*) 
                    FROM audit_logs al 
                    WHERE al.user_id = la.user_id 
                      AND al.operation_time >= DATE_SUB(NOW(), INTERVAL ? MINUTE)
                ) AS actions_in_window,
                la.url AS last_url,
                la.method AS last_method,
                la.client_ip,
                la.user_agent,
                la.browser,
                la.operating_system,
                la.location,
                la.change_summary,
                COALESCE(ltv.session_id, lta.session_id) AS session_id,
                COALESCE(ltv.last_active_time, lta.last_active_time) AS session_last_active_time,
                TIMESTAMPDIFF(MINUTE, COALESCE(ltv.created_at, lta.created_at), NOW()) AS session_duration_minutes,
                CASE 
                    WHEN ltv.session_id IS NOT NULL THEN 'valid'
                    WHEN lta.session_id IS NOT NULL AND lta.expires_at <= NOW() THEN 'expired'
                    WHEN lta.session_id IS NULL THEN 'none'
                    ELSE 'unknown'
                END AS token_status
            FROM latest_audit la
            LEFT JOIN latest_token_valid ltv ON ltv.user_id = la.user_id AND ltv.rn = 1
            LEFT JOIN latest_token_any lta ON lta.user_id = la.user_id AND lta.rn = 1
            WHERE la.rn = 1
            ORDER BY last_active_time DESC
        `

        try {
            const [rows] = await db.execute(sql, [minutes, minutes])
            return rows
        } catch (error) {
            logger.error('获取在线用户失败', { error })
            throw error
        }
    }

    // 获取仪表盘统计数据
    static async getDashboardStatistics(timeRange = 'week', startDate = null, endDate = null) {
        // 构建日期过滤条件
        const dateFilter = {};
        if (startDate && endDate) {
            dateFilter.start_time = startDate;
            dateFilter.end_time = endDate;
        } else {
            // 根据时间范围设置默认日期
            const now = new Date();
            let startDate;
            if (timeRange === 'week') {
                startDate = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
            } else if (timeRange === 'month') {
                startDate = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
            } else { // year
                startDate = new Date(now.getTime() - 365 * 24 * 60 * 60 * 1000);
            }
            dateFilter.start_time = startDate.toISOString().split('T')[0];
            dateFilter.end_time = now.toISOString().split('T')[0];
        }

        // 并行获取所有统计数据
        const [totalUsersCount, onlineUsersCount, visitStats, visitTrendData, 
                userDistributionData, pageStatsData, growthData] = await Promise.all([
            this.getTotalUsersCount(),
            this.getOnlineUsersCount(),
            this.getVisitStatistics(dateFilter.start_time, dateFilter.end_time),
            this.getVisitTrendData(timeRange),
            this.getDeviceDistribution(dateFilter.start_time, dateFilter.end_time),
            this.getPageStatistics(dateFilter.start_time, dateFilter.end_time),
            this.getCumulativeUserGrowth()
        ]);

        return {
            stats: {
                totalUsers: totalUsersCount,
                onlineUsers: onlineUsersCount,
                totalVisits: visitStats.totalVisits,
                avgDuration: visitStats.avgDuration
            },
            visitTrendData,
            userDistributionData,
            pageStatsData,
            growthData
        };
    }

    // 获取总用户数
    static async getTotalUsersCount() {
        const [rows] = await db.execute('SELECT COUNT(*) as count FROM user WHERE is_delete = 0');
        return rows[0].count || 0;
    }

    // 获取在线用户数
    static async getOnlineUsersCount() {
        const [rows] = await db.execute(
            'SELECT COUNT(DISTINCT user_id) as count FROM audit_logs WHERE operation_time >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) AND user_id IS NOT NULL'
        );
        return rows[0].count || 0;
    }

    // 获取访问统计（总访问量和平均访问时长）
    static async getVisitStatistics(startDate, endDate) {
        const [rows] = await db.execute(
            `SELECT 
                COUNT(*) as totalVisits,
                AVG(TIMESTAMPDIFF(SECOND, created_at, operation_time)) as avgDurationSeconds
            FROM audit_logs 
            WHERE operation_time >= ? AND operation_time <= ?`,
            [startDate + ' 00:00:00', endDate + ' 23:59:59']
        );

        let avgDuration = '0s';
        const avgSeconds = rows[0].avgDurationSeconds;
        if (avgSeconds && avgSeconds > 0) {
            const minutes = Math.floor(avgSeconds / 60);
            const seconds = Math.floor(avgSeconds % 60);
            avgDuration = minutes > 0 ? `${minutes}m ${seconds}s` : `${seconds}s`;
        }

        return {
            totalVisits: rows[0].totalVisits || 0,
            avgDuration
        };
    }

    // 获取访问趋势数据
    static async getVisitTrendData(timeRange) {
        let visitTrendData = [];

        if (timeRange === 'week') {
            // 获取最近7天的访问数据
            for (let i = 6; i >= 0; i--) {
                const date = new Date();
                date.setDate(date.getDate() - i);
                const dateStr = ['周日', '周一', '周二', '周三', '周四', '周五', '周六'][date.getDay()];
                const dateFormat = date.toISOString().split('T')[0];
                
                const [dayVisits] = await db.execute(
                    'SELECT COUNT(*) as count, COUNT(DISTINCT user_id) as userCount FROM audit_logs WHERE DATE(operation_time) = ?',
                    [dateFormat]
                );
                
                visitTrendData.push({ date: dateStr, value: dayVisits[0].count, type: '访问量' });
                visitTrendData.push({ date: dateStr, value: dayVisits[0].userCount, type: '用户数' });
            }
        } else if (timeRange === 'month') {
            // 获取最近4周的访问数据
            for (let i = 3; i >= 0; i--) {
                const weekStart = new Date();
                weekStart.setDate(weekStart.getDate() - (i + 1) * 7);
                const weekEnd = new Date(weekStart);
                weekEnd.setDate(weekEnd.getDate() + 6);
                
                const [weekVisits] = await db.execute(
                    'SELECT COUNT(*) as count, COUNT(DISTINCT user_id) as userCount FROM audit_logs WHERE DATE(operation_time) BETWEEN ? AND ?',
                    [weekStart.toISOString().split('T')[0], weekEnd.toISOString().split('T')[0]]
                );
                
                visitTrendData.push({ date: `第${4-i}周`, value: weekVisits[0].count, type: '访问量' });
                visitTrendData.push({ date: `第${4-i}周`, value: weekVisits[0].userCount, type: '用户数' });
            }
        } else { // year
            // 获取最近12个月的访问数据
            for (let i = 11; i >= 0; i--) {
                const month = new Date();
                month.setMonth(month.getMonth() - i);
                const monthStr = month.getMonth() + 1 + '月';
                const monthFormat = month.getFullYear() + '-' + String(month.getMonth() + 1).padStart(2, '0');
                
                const [monthVisits] = await db.execute(
                    'SELECT COUNT(*) as count, COUNT(DISTINCT user_id) as userCount FROM audit_logs WHERE DATE_FORMAT(operation_time, "%Y-%m") = ?',
                    [monthFormat]
                );
                
                visitTrendData.push({ date: monthStr, value: monthVisits[0].count, type: '访问量' });
                visitTrendData.push({ date: monthStr, value: monthVisits[0].userCount, type: '用户数' });
            }
        }

        return visitTrendData;
    }

    // 获取用户分布数据（按设备类型）
    static async getDeviceDistribution(startDate, endDate) {
        const [rows] = await db.execute(
            `SELECT 
                device_type,
                COUNT(DISTINCT user_id) as count
            FROM audit_logs 
            WHERE operation_time >= ? AND operation_time <= ?
            GROUP BY device_type
            ORDER BY count DESC`,
            [startDate + ' 00:00:00', endDate + ' 23:59:59']
        );
        
        const distributionData = rows.map(item => ({
            type: item.device_type || '未知',
            value: item.count
        }));
        
        // 如果没有数据，使用默认分布
        if (distributionData.length === 0) {
            return [
                { type: '移动端', value: 62 },
                { type: '桌面端', value: 32 },
                { type: '平板', value: 6 }
            ];
        }
        
        return distributionData;
    }

    // 获取页面访问统计（PV/UV）
    static async getPageStatistics(startDate, endDate) {
        const [rows] = await db.execute(
            `SELECT 
                url as category,
                COUNT(*) as pv,
                COUNT(DISTINCT user_id) as uv
            FROM audit_logs 
            WHERE operation_time >= ? AND operation_time <= ?
            GROUP BY url
            ORDER BY pv DESC
            LIMIT 5`,
            [startDate + ' 00:00:00', endDate + ' 23:59:59']
        );
        
        const pageStatsData = [];
        rows.forEach(item => {
            // 简化URL为页面名称
            let category = item.category || '其他';
            if (category.includes('dashboard')) category = '首页';
            else if (category.includes('product')) category = '产品';
            else if (category.includes('article') || category.includes('news')) category = '资讯';
            else if (category.includes('user')) category = '用户';
            else if (category.includes('setting')) category = '设置';
            
            pageStatsData.push({ category, value: item.pv, type: 'PV' });
            pageStatsData.push({ category, value: item.uv, type: 'UV' });
        });
        
        // 如果没有数据，使用默认数据
        if (pageStatsData.length === 0) {
            const defaultPages = ['首页', '产品', '资讯', '用户', '设置'];
            defaultPages.forEach(page => {
                pageStatsData.push({ category: page, value: Math.floor(Math.random() * 2000) + 1000, type: 'PV' });
                pageStatsData.push({ category: page, value: Math.floor(Math.random() * 1000) + 500, type: 'UV' });
            });
        }
        
        return pageStatsData;
    }

    // 获取累计用户趋势（近12个月）
    static async getCumulativeUserGrowth() {
        const growthData = [];
        for (let i = 11; i >= 0; i--) {
            const month = new Date();
            month.setMonth(month.getMonth() - i);
            const monthStr = month.getMonth() + 1 + '月';
            const monthEnd = new Date(month.getFullYear(), month.getMonth() + 1, 0);
            
            const [rows] = await db.execute(
                'SELECT COUNT(*) as count FROM user WHERE created_at <= ? AND is_delete = 0',
                [monthEnd]
            );
            
            growthData.push({ date: monthStr, value: rows[0].count || 0 });
        }
        return growthData;
    }
}

module.exports = AuditLogModel